
-- **************************************************************************
--    Project Name:   服务质量-仲裁分析日报汇总监控机器人推送
--    Job Name:       jms_dm__dm_sqs_arbi_analysis_network_sum_all_dt
--    Author :        季修魁
--    date：          2023/07/05
--    table：         jms_dm.dm_sqs_arbi_analysis_network_sum_dt/jms_dm.dm_sqs_arbi_analysis_center_declare_sum_dt
--                    jms_dm.dm_sqs_arbi_analysis_network_type_sum_dt/jms_dm.dm_sqs_arbi_analysis_delay_shipment_sum_dt
-- **************************************************************************
-- **************************************************************************

set hive.exec.dynamic.partition=true;             --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; --  每天生成 60 个分区

----1.dm_sqs_arbi_analysis_network_sum_dt

insert overwrite table jms_dm.dm_sqs_arbi_analysis_network_sum_dt partition (dt)
select a.stat_Date
       ,a.network_code
       ,a.network_name
       ,a.network_type
       ,a.virt_code
       ,a.virt_name
       ,a.agent_code
       ,a.agent_name
       ,a.zone_code
       ,a.zone_name
       ,sum(a.declare_arb_cnt ) as declare_arb_cnt
       ,sum(a.end_return_amt  ) as end_return_amt
       ,sum(a.duty_arb_cnt    ) as duty_arb_cnt
       ,sum(a.end_fine_amt    ) as end_fine_amt
       ,sum(a.overtime_cnt    ) as overtime_cnt
       ,sum(a.overtime_amt    ) as overtime_amt
       ,a.first_type_code
       ,a.first_type
       ,a.second_type_code
       ,a.second_type
       ,b.parent_area_code
       ,b.parent_area_name
       ,sum(a.invalid_reply_cnt) as invalid_reply_cnt
       ,dt
  from
(
select to_date(create_time)                         as stat_Date
       ,create_network_code                         as network_code
       ,create_network_name                         as network_name
       ,create_network_type                         as network_type
       ,create_virt_code                            as virt_code
       ,create_virt_name                            as virt_name
       ,create_agent_code                           as agent_code
       ,create_agent_name                           as agent_name
       ,create_zone_code                            as zone_code
       ,create_zone_name                            as zone_name
       ,count(distinct code)                        as declare_arb_cnt
       ,0                                           as end_return_amt
       ,0                                           as duty_arb_cnt
       ,0                                           as end_fine_amt
       ,0                                           as overtime_cnt
       ,0                                           as overtime_amt
       ,0                                           as invalid_reply_cnt
       ,first_type_code                             as first_type_code
       ,first_type                                  as first_type
       ,second_type_code                            as second_type_code
       ,second_type                                 as second_type
       ,to_date(create_time)                        as dt
 from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt>=trunc('{{ execution_date | cst_ds }}','MM') and dt<='{{ execution_date | cst_ds }}'
   and to_date(create_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(create_time)<='{{ execution_date | cst_ds }}'
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(create_time)
          ,create_network_code
          ,create_network_name
          ,create_network_type
          ,create_virt_code
          ,create_virt_name
          ,create_agent_code
          ,create_agent_name
          ,create_zone_code
          ,create_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type

----2.申报返款
union all

select to_date(closing_time)                           as stat_Date
       ,create_network_code                            as network_code
       ,create_network_name                            as network_name
       ,create_network_type                            as network_type
       ,create_virt_code                               as virt_code
       ,create_virt_name                               as virt_name
       ,create_agent_code                              as agent_code
       ,create_agent_name                              as agent_name
       ,create_zone_code                               as zone_code
       ,create_zone_name                               as zone_name
       ,0                                              as declare_arb_cnt
       ,SUM(payable_amount_total/duty_network_count)   as end_return_amt
       ,0                                              as duty_arb_cnt
       ,0                                              as end_fine_amt
       ,0                                              as overtime_cnt
       ,0                                              as overtime_amt
       ,0                                              as invalid_reply_cnt
       ,first_type_code                                as first_type_code
       ,first_type                                     as first_type
       ,second_type_code                               as second_type_code
       ,second_type                                    as second_type
       ,to_date(closing_time)                          as dt
 from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt >= add_months(trunc('{{ execution_date | cst_ds }}','MM'),-3)  and dt<='{{ execution_date | cst_ds }}'
   and to_date(closing_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(closing_time)<='{{ execution_date | cst_ds }}'
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(closing_time)
          ,create_network_code
          ,create_network_name
          ,create_network_type
          ,create_virt_code
          ,create_virt_name
          ,create_agent_code
          ,create_agent_name
          ,create_zone_code
          ,create_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type

----3.责任票数

union all
select to_date(create_time)                         as stat_Date
       ,duty_network_code                           as network_code
       ,duty_network_name                           as network_name
       ,duty_network_type                           as network_type
       ,duty_virt_code                              as virt_code
       ,duty_virt_name                              as virt_name
       ,duty_agent_code                             as agent_code
       ,duty_agent_name                             as agent_name
       ,duty_zone_code                              as zone_code
       ,duty_zone_name                              as zone_name
       ,0                                           as declare_arb_cnt
       ,0                                           as end_return_amt
       ,sum(1/duty_network_count)                   as duty_arb_cnt
       ,0                                           as end_fine_amt
       ,0                                           as overtime_cnt
       ,0                                           as overtime_amt
       ,0                                           as invalid_reply_cnt
       ,first_type_code                             as first_type_code
       ,first_type                                  as first_type
       ,second_type_code                            as second_type_code
       ,second_type                                 as second_type
       ,to_date(create_time)                        as dt
 from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt>=trunc('{{ execution_date | cst_ds }}','MM') and dt<='{{ execution_date | cst_ds }}'
   and to_date(create_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(create_time)<='{{ execution_date | cst_ds }}'
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(create_time)
          ,duty_network_code
          ,duty_network_name
          ,duty_network_type
          ,duty_virt_code
          ,duty_virt_name
          ,duty_agent_code
          ,duty_agent_name
          ,duty_zone_code
          ,duty_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type
---4.罚款、超时
union all

select to_date(closing_time)                         as stat_Date
       ,duty_network_code                           as network_code
       ,duty_network_name                           as network_name
       ,duty_network_type                           as network_type
       ,duty_virt_code                              as virt_code
       ,duty_virt_name                              as virt_name
       ,duty_agent_code                             as agent_code
       ,duty_agent_name                             as agent_name
       ,duty_zone_code                              as zone_code
       ,duty_zone_name                              as zone_name
       ,0                                           as declare_arb_cnt
       ,0                                           as end_return_amt
       ,0                                           as duty_arb_cnt
       ,SUM(award_amount_total  /duty_network_count)     as end_fine_amt
       ,sum(case when status=10 and responsibility_reply_status =1 and responsibility_confirm_status =1 then (1/duty_network_count) else 0 end  )                 as overtime_cnt
       ,sum(case when status=10 and responsibility_reply_status =1 and responsibility_confirm_status =1 then (handling_fee_total/duty_network_count) else 0 end ) as overtime_amt
       ,sum(case when status=10 and process_user_code<>'sys' and responsibility_reply_status =2 and responsibility_confirm_status=1
                  and handling_fee_total in (0,10)
                 then (1/duty_network_count) else 0 end
            ) as invalid_reply_cnt
       ,first_type_code
       ,first_type
       ,second_type_code
       ,second_type
       ,to_date(closing_time)   as dt
  from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt >= add_months(trunc('{{ execution_date | cst_ds }}','MM'),-3)  and dt<='{{ execution_date | cst_ds }}'
   and to_date(closing_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(closing_time)<='{{ execution_date | cst_ds }}'
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(closing_time)
          ,duty_network_code
          ,duty_network_name
          ,duty_network_type
          ,duty_virt_code
          ,duty_virt_name
          ,duty_agent_code
          ,duty_agent_name
          ,duty_zone_code
          ,duty_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type
) a
left join jms_dim.dim_network_whole_massage b
  on a.network_code = b.code
group by a.stat_Date
       ,a.network_code
       ,a.network_name
       ,a.network_type
       ,a.virt_code
       ,a.virt_name
       ,a.agent_code
       ,a.agent_name
       ,a.zone_code
       ,a.zone_name
       ,a.first_type_code
       ,a.first_type
       ,a.second_type_code
       ,a.second_type
       ,a.dt
       ,b.parent_area_code
       ,b.parent_area_name
distribute by dt
;

---2.dm_sqs_arbi_analysis_center_declare_sum_dt

insert overwrite table jms_dm.dm_sqs_arbi_analysis_center_declare_sum_dt partition (dt)
select to_date(create_time)             as stat_date
       ,create_agent_code               as declare_agent_code
       ,create_agent_name               as declare_agent_name
       ,duty_network_code               as duty_network_code
       ,duty_network_name               as duty_network_name
       ,duty_network_type                as duty_network_type
       ,duty_manage_code                as duty_manage_code
       ,duty_manage_name                as duty_manage_name
       ,duty_virt_code                    as duty_virt_code
       ,duty_virt_name                    as duty_virt_name
       ,duty_agent_code                    as duty_agent_code
       ,duty_agent_name                    as duty_agent_name
       ,duty_zone_code                    as duty_zone_code
       ,duty_zone_name                    as duty_zone_name
       ,sum((1/duty_network_count))     as duty_arb_cnt
       ,first_type_code                             as first_type_code
       ,first_type                                  as first_type
       ,second_type_code                            as second_type_code
       ,second_type                                 as second_type
       ,to_date(create_time)            as dt
  from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt>=trunc('{{ execution_date | cst_ds }}','MM') and dt<='{{ execution_date | cst_ds }}'
   and to_date(create_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(create_time)<='{{ execution_date | cst_ds }}'
   and first_type='延误'
   and duty_network_type='4'
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(create_time)
          ,create_agent_code
          ,create_agent_name
          ,duty_network_code
          ,duty_network_name
          ,duty_network_type
          ,duty_manage_code
          ,duty_manage_name
          ,duty_virt_code
          ,duty_virt_name
          ,duty_agent_code
          ,duty_agent_name
          ,duty_zone_code
          ,duty_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type
distribute by dt
;

---3.dm_sqs_arbi_analysis_network_type_sum_dt
---申报
insert overwrite table jms_dm.dm_sqs_arbi_analysis_network_type_sum_dt partition (dt)
select stat_date
       ,network_code
       ,network_name
       ,type
       ,manage_code
       ,manage_name
       ,virt_code
       ,virt_name
       ,agent_code
       ,agent_name
       ,zone_code
       ,zone_name
       ,sum(arb_cnt)       as arb_cnt
       ,sum(arb_fine_amt)  as arb_fine_amt
       ,first_type_code
       ,first_type
       ,second_type_code
       ,second_type
       ,stat_date       as dt
  from
(
select to_date(create_time)             as stat_date
       ,create_network_code             as network_code
       ,create_network_name             as network_name
       ,create_manage_code              as manage_code
       ,create_manage_name              as manage_name
       ,create_virt_code                as virt_code
       ,create_virt_name                as virt_name
       ,create_agent_code               as agent_code
       ,create_agent_name               as agent_name
       ,create_zone_code                as zone_code
       ,create_zone_name                as zone_name
       ,'1'                             as type
       ,first_type_code
       ,first_type
       ,second_type_code
       ,second_type
       ,count(distinct code)            as arb_cnt
       ,0                               as arb_fine_amt
       ,to_date(create_time)            as dt
  from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt>=trunc('{{ execution_date | cst_ds }}','MM') and dt<='{{ execution_date | cst_ds }}'
   and to_date(create_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(create_time)<='{{ execution_date | cst_ds }}'
   and create_network_type in ('5','6')
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(create_time)
          ,create_network_code
          ,create_network_name
          ,create_manage_code
          ,create_manage_name
          ,create_virt_code
          ,create_virt_name
          ,create_agent_code
          ,create_agent_name
          ,create_zone_code
          ,create_zone_name
          ,'1'
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type
---责任票数
 union all
select to_date(create_time)             as stat_date
       ,duty_network_code               as network_code
       ,duty_network_name               as network_name
       ,duty_manage_code                as manage_code
       ,duty_manage_name                as manage_name
       ,duty_virt_code                  as virt_code
       ,duty_virt_name                  as virt_name
       ,duty_agent_code                 as agent_code
       ,duty_agent_name                 as agent_name
       ,duty_zone_code                  as zone_code
       ,duty_zone_name                  as zone_name
       ,'2'                             as type
       ,first_type_code
       ,first_type
       ,second_type_code
       ,second_type
       ,sum((1/duty_network_count))    as arb_cnt
       ,0                              as arb_fine_amt
       ,to_date(create_time)           as dt
  from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt>=trunc('{{ execution_date | cst_ds }}','MM') and dt<='{{ execution_date | cst_ds }}'
   and to_date(create_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(create_time)<='{{ execution_date | cst_ds }}'
   and duty_network_type in ('5','6')
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(create_time)
          ,duty_network_code
          ,duty_network_name
          ,duty_manage_code
          ,duty_manage_name
          ,duty_virt_code
          ,duty_virt_name
          ,duty_agent_code
          ,duty_agent_name
          ,duty_zone_code
          ,duty_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type

---责任罚款
 union all
select to_date(closing_time)             as stat_date
       ,duty_network_code               as network_code
       ,duty_network_name               as network_name
       ,duty_manage_code                as manage_code
       ,duty_manage_name                as manage_name
       ,duty_virt_code                  as virt_code
       ,duty_virt_name                  as virt_name
       ,duty_agent_code                 as agent_code
       ,duty_agent_name                 as agent_name
       ,duty_zone_code                  as zone_code
       ,duty_zone_name                  as zone_name
       ,'2'                             as type
       ,first_type_code
       ,first_type
       ,second_type_code
       ,second_type
       ,0                               as arb_cnt
       ,sum((award_amount_total/duty_network_count))          as arb_fine_amt
       ,to_date(closing_time)           as dt
  from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt >= add_months(trunc('{{ execution_date | cst_ds }}','MM'),-3) and dt <='{{ execution_date | cst_ds }}'
   and to_date(closing_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND to_date(closing_time)<='{{ execution_date | cst_ds }}'
   and duty_network_type in ('5','6')
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(closing_time)
          ,duty_network_code
          ,duty_network_name
          ,duty_manage_code
          ,duty_manage_name
          ,duty_virt_code
          ,duty_virt_name
          ,duty_agent_code
          ,duty_agent_name
          ,duty_zone_code
          ,duty_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type

---超时票数、罚款
 union all
 select to_date(closing_time)             as stat_date
       ,duty_network_code               as network_code
       ,duty_network_name               as network_name
       ,duty_manage_code                as manage_code
       ,duty_manage_name                as manage_name
       ,duty_virt_code                  as virt_code
       ,duty_virt_name                  as virt_name
       ,duty_agent_code                 as agent_code
       ,duty_agent_name                 as agent_name
       ,duty_zone_code                  as zone_code
       ,duty_zone_name                  as zone_name
       ,'3'                             as type
       ,first_type_code
       ,first_type
       ,second_type_code
       ,second_type
       ,sum(case when status=10 and responsibility_reply_status =1 and responsibility_confirm_status =1 then (1/duty_network_count) else 0 end  )               as arb_cnt
       ,sum(case when status=10 and responsibility_reply_status =1 and responsibility_confirm_status =1 then handling_fee_total/duty_network_count else 0 end ) as arb_fine_amt
       ,to_date(closing_time)           as dt
  from jms_dm.dm_sqs_arbi_analysis_detail_dt
 where dt >= add_months(trunc('{{ execution_date | cst_ds }}','MM'),-3) and dt <='{{ execution_date | cst_ds }}'
   and to_date(closing_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND to_date(closing_time)<='{{ execution_date | cst_ds }}'
   and duty_network_type in ('5','6')
   and status <>2
   and first_type_code not in ('17','25')   ---剔除掉一级类型是“疑似遗失（编码17）”和“延误-自动抓取（编码25）”的数据  add jixk 20231124
 group by to_date(closing_time)
          ,duty_network_code
          ,duty_network_name
          ,duty_manage_code
          ,duty_manage_name
          ,duty_virt_code
          ,duty_virt_name
          ,duty_agent_code
          ,duty_agent_name
          ,duty_zone_code
          ,duty_zone_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type
 ) t
group by stat_date
         ,network_code
         ,network_name
         ,manage_code
         ,manage_name
         ,virt_code
         ,virt_name
         ,agent_code
         ,agent_name
         ,zone_code
         ,zone_name
         ,type
         ,first_type_code
         ,first_type
         ,second_type_code
         ,second_type
 distribute by dt
;


---4.dm_sqs_arbi_analysis_delay_shipment_sum_dt

insert overwrite table  jms_dm.dm_sqs_arbi_analysis_delay_shipment_sum_dt partition (dt)
select to_date(create_time)             as stat_date
       ,duty_network_code               as network_code
       ,duty_network_name               as network_name
       ,duty_manage_code                as manage_code
       ,duty_manage_name                as manage_name
       ,duty_virt_code                  as virt_code
       ,duty_virt_name                  as virt_name
       ,duty_agent_code                 as agent_code
       ,duty_agent_name                 as agent_name
       ,sum((1/duty_network_count))    as arb_cnt
       ,0                              as arb_fine_amt
       ,first_type_code
       ,first_type
       ,second_type_code
       ,second_type
       ,first_center_loading_send_shipment_no
       ,first_center_loading_send_shipment_name
       ,to_date(create_time)           as dt
  from jms_dm.dm_sqs_arbi_analysis_detail_dt a
 where dt>=trunc('{{ execution_date | cst_ds }}','MM')
   and dt<='{{ execution_date | cst_ds }}'
   and to_date(create_time)>=trunc('{{ execution_date | cst_ds }}','MM') AND  to_date(create_time)<='{{ execution_date | cst_ds }}' and duty_network_type ='4'
   and status <>2
   and first_type='延误'
   and first_center_loading_send_shipment_name is not null
 group by to_date(create_time)
          ,duty_network_code
          ,duty_network_name
          ,duty_manage_code
          ,duty_manage_name
          ,duty_virt_code
          ,duty_virt_name
          ,duty_agent_code
          ,duty_agent_name
          ,first_type_code
          ,first_type
          ,second_type_code
          ,second_type
          ,first_center_loading_send_shipment_no
          ,first_center_loading_send_shipment_name
distribute by dt
;